Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Configuration
Imports System.Text

Public Class GetData
    Public strProdNM, ProdID As String
    Public IntPrice As Double
    Public strDesc As String
    Dim _strPath As String
    Dim strErrEx As String
    Dim strTDErr As String
    Dim arrProd(3) As String
    Dim strError As String
    Dim objDataView As DataView
    Dim intRowCnt, i As Integer
    Public DBConnection As String

    Public Sub Dataset(ByVal strSQL As String, ByVal strTblName As String, ByRef strError As String, ByRef oDS As DataSet)
        ' This subroutine takes in the environment(D for development P for production)
        ' the user ID, SQL needed for query 
        Dim cmd As SqlDataAdapter
        Dim Ds As New DataSet
        Dim strLO As String

        strLO = GetConnectstring()

        Dim Conn As New SqlConnection(strLO)

        'Conn.ConnectionTimeout = 240

        cmd = New SqlDataAdapter(strSQL, Conn)
        cmd.SelectCommand.CommandTimeout = 0

        Try
            cmd.Fill(oDS, strTblName)
        Catch ex As SqlException

            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            strError = ex.Message '& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            strError = ExpError(strTDErr, ex)


        Finally
            If Not (Conn Is Nothing) Then
                Conn.Close()
            End If
        End Try
        ' below 2  lines for testing purposes only
        'Dim intCnt As Integer
        'intCnt = oDS.Tables(strTblName).Rows.Count
    End Sub

    Public Sub ExecuteSQL(ByVal strSQL As String, ByRef strError As String)
        ' This subroutine takes in the environment(D for development P for production)
        ' the user ID, SQL needed for query 
        Dim cmd As SqlCommand
        Dim Ds As New DataSet
        Dim strLO As String
        'TDUID = UserID
        strLO = GetConnectstring()
        Dim Conn As New SqlConnection(strLO)
        'Conn.ConnectionTimeout = 240
        cmd = New SqlCommand(strSQL, Conn)
        cmd.CommandTimeout = 0

        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            strError = ex.Message ''& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            strError = ExpError(strTDErr, ex)

        Finally
            If Not (Conn Is Nothing) Then
                Conn.Close()
            End If
        End Try

    End Sub

    Public Function DataReader(ByVal strSQL As String, ByRef strError As String) As SqlDataReader

        Dim strLO As String
        'TDUID = UserID
        strLO = GetConnectstring()
        'strLO = DBConnection
        'strLO = "Data Source=YIN_MOBILE;Initial Catalog=gaglobal10;Integrated Security=SSPI"
        Dim Conn As New SqlConnection(strLO)
        'Conn.ConnectionTimeout = 240
        Dim cmd As New SqlCommand(strSQL, Conn)
        Dim reader As SqlDataReader = Nothing
        cmd.CommandType = CommandType.Text
        cmd.CommandTimeout = 0
        Try
            Conn.Open()
            reader = cmd.ExecuteReader
            If IsNothing(reader) Then
                strError = "No data found"
            End If
            'Return reader
            'GetDataReader = reader
        Catch ex As SqlException
            'Dim strErrEx As String
            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            strError = strTDErr
            'strError = ex.Message '& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            'strError = ExpError(strTDErr, ex)

        End Try
        Return reader
    End Function

    Public Function GetConnectstring() As String
        'Dim strConn = "Data Source=VCNSQL89;Initial Catalog=gaglobal10;User ID=gaadmin10;Password=admin606"

        'Dim strConn = "Data Source=(Local); Initial Catalog=gaglobal10;Trusted_Connection=true;"
        'Dim strConn = "Data Source=(local);Initial Catalog=gaglobal10;integrated Security=SSPI"
        'Dim strConn = "server=(local); database=pubs;Integrated Security=SSPI"
        Dim strConn = "Data Source=VCNSQL89;Initial Catalog=gaglobal10;User ID=gaadmin10;Password=admin606"
        Return strConn
    End Function



    'Public Sub LogError(ByVal strError As String)
    'FDSLogger.warn(strError)
    'End Sub
    Private Function ExpError(ByVal strTDErr As String, ByVal ex As SqlException) As String

        'Dim strError As String
        Dim strErrEx As String

        Select Case strTDErr
            Case "-3004"
                strErrEx = "Teradata User Access not authorized. "
            Case "-3003"
                strErrEx = "Invalid Teradata User Access. "
            Case "-2803"
                strErrEx = "Duplicate key entry on insert. Please report page. "
            Case "0"
                'strErrEx = "Teradata Timeout has occurred. "
                strErrEx = "Teradata is busy with other process. Wait a few minutes and retry your query."
            Case "-3706"
                strErrEx = "SQL Syntax Error. Please report page and search criteria used. "
            Case "-3707"
                strErrEx = "SQL Syntax Error. Please report page and search criteria used. "
            Case Else
                strErrEx = "Teradata Error. Please report page and search criteria used. "
        End Select
        ''/**use this code to debug**//
        ' strError = ex.Message & vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf


        ''Throw customException
        Return strErrEx
    End Function

    Public Sub es(ByVal preparedStatement As PreparedStatement, ByRef strError As String)
        ' This subroutine takes in the environment(D for development P for production)
        ' the user ID, SQL needed for query 
        Dim cmd As SqlCommand
        Dim Ds As New DataSet
        Dim strLO As String
        'TDUID = UserID
        strLO = GetConnectstring()
        Dim Conn As New SqlConnection(strLO)
        'Conn.ConnectionTimeout = 240
        cmd = preparedStatement.getSqlCommand(Conn)
        'cmd = New SqlCommand(strSQL, conn,objrd)
        cmd.CommandTimeout = 0

        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            strError = ex.Message ''& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            strError = ExpError(strTDErr, ex)

        Finally
            If Not (Conn Is Nothing) Then
                Conn.Close()
            End If
        End Try

    End Sub
    Public Function getConn()
        Dim strLO As String
        strLO = GetConnectstring()
        Dim Conn As New SqlConnection(strLO)
        Conn.Open()
        Return Conn

    End Function

    Public Sub closeConn(conn As SqlConnection)
        If Not (conn Is Nothing) Then
            conn.Close()
        End If
    End Sub
    Public Function dr(ByVal preparedStatement As PreparedStatement, ByRef strError As String, conn As SqlConnection, reader As SqlDataReader) As SqlDataReader

        'Dim strLO As String
        'TDUID = UserID
        'strLO = GetConnectstring()
        'strLO = DBConnection
        'strLO = "Data Source=YIN_MOBILE;Initial Catalog=gaglobal10;Integrated Security=SSPI"
        'Dim Conn As New SqlConnection(strLO)
        'Conn.ConnectionTimeout = 240
        Dim cmd As SqlCommand
        cmd = preparedStatement.getSqlCommand(conn)
        'Dim cmd As New SqlCommand(strSQL, conn,objrd)
        If Not reader Is Nothing Then
            If Not reader.IsClosed Then
                reader.Close()
            End If
        End If
        cmd.CommandType = CommandType.Text
        cmd.CommandTimeout = 0
        Try
            'Conn.Open()
            reader = cmd.ExecuteReader
            If IsNothing(reader) Then
                strError = "No data found"
            End If
            'Return reader
            'GetDataReader = reader
        Catch ex As SqlException
            'Dim strErrEx As String
            Dim strTDErr As String = ""
            Dim i As Integer
            'Conn.Close()
            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            strError = strTDErr
            'strError = ex.Message '& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            'strError = ExpError(strTDErr, ex)

        End Try
        Return reader
    End Function
End Class
